package export;


import beans.User;
import dbutils.DBHelper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import service.LogService;

import javax.swing.*;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ExportToExcel {
    //导出个人的日志
    public void ExportLogToExcel(User user) throws SQLException {
        Connection connection = DBHelper.getConn();

        PreparedStatement statement = null;
        ResultSet resultSet = null;
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("personalLog");
        try {
            statement = connection.prepareStatement("SELECT * FROM logs where userName = ?");
            statement.setString(1, String.valueOf(user.getName()));
            resultSet = statement.executeQuery();
            Row headerRow = sheet.createRow(0);
            Cell headerCell0 = headerRow.createCell(0);
            headerCell0.setCellValue("用户名");
            Cell headerCell1 = headerRow.createCell(1);
            headerCell1.setCellValue("时间");
            Cell headerCell2 = headerRow.createCell(2);
            headerCell2.setCellValue("日志内容");
            int rownum = 1;
            while (resultSet.next()) {
                Row row = sheet.createRow(rownum);
                Cell cell0 = row.createCell(0);
                cell0.setCellValue(resultSet.getString("userName"));
                Cell cell1 = row.createCell(1);
                cell1.setCellValue(resultSet.getString("times"));
                Cell cell2 = row.createCell(2);
                cell2.setCellValue(resultSet.getString("logContext"));
                rownum++;
            }

            File file = new File("personalLog.xls");
            FileOutputStream out = new FileOutputStream(file);
            workbook.write(out);
            out.close();
            JOptionPane.showMessageDialog(null,"导出成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeAll(connection,statement,resultSet);
        }
    }

    //管理员导出所有日志
    public void exportAllLogsToExcel() throws SQLException {
        Connection connection = DBHelper.getConn();

        PreparedStatement statement = null;
        ResultSet resultSet = null;
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("allLogs");
        try {
            statement = connection.prepareStatement("SELECT * FROM logs;");
            resultSet = statement.executeQuery();
            Row headerRow = sheet.createRow(0);
            Cell headerCell0 = headerRow.createCell(0);
            headerCell0.setCellValue("用户名");
            Cell headerCell1 = headerRow.createCell(1);
            headerCell1.setCellValue("时间");
            Cell headerCell2 = headerRow.createCell(2);
            headerCell2.setCellValue("日志内容");
            int rownum = 1;
            while (resultSet.next()) {
                Row row = sheet.createRow(rownum);
                Cell cell0 = row.createCell(0);
                cell0.setCellValue(resultSet.getString("userName"));
                Cell cell1 = row.createCell(1);
                cell1.setCellValue(resultSet.getString("times"));
                Cell cell2 = row.createCell(2);
                cell2.setCellValue(resultSet.getString("logContext"));
                rownum++;
            }

            File file = new File("allLogs.xls");
            FileOutputStream out = new FileOutputStream(file);
            workbook.write(out);
            out.close();
            JOptionPane.showMessageDialog(null,"导出成功！");
            //添加日志
            User admin = new User();
            LogService.addLogs(admin,"管理员 " + admin + "导出了所有日志 ");

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeAll(connection,statement,resultSet);
        }
    }


    //导出顾客订单
    public void exportCOrdersToExcel(User user) throws SQLException {
        Connection connection = DBHelper.getConn();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("customerPersonalOrders");
        try {
            statement = connection.prepareStatement("SELECT * FROM corders where user = ?");
            statement.setString(1, String.valueOf(user.getName()));
            resultSet = statement.executeQuery();

            Row headerRow = sheet.createRow(0);
            Cell headerCell0 = headerRow.createCell(0);
            headerCell0.setCellValue("用户");
            Cell headerCell1 = headerRow.createCell(1);
            headerCell1.setCellValue("时间");
            Cell headerCell2 = headerRow.createCell(2);
            headerCell2.setCellValue("商品名");
            Cell headerCell3 = headerRow.createCell(3);
            headerCell3.setCellValue("价格");
            Cell headerCell4 = headerRow.createCell(4);
            headerCell4.setCellValue("商家");
            Cell headerCell5 = headerRow.createCell(5);
            headerCell5.setCellValue("生产者");

            int rownum = 1;
            while (resultSet.next()) {
                Row row = sheet.createRow(rownum);
                Cell cell0 = row.createCell(0);
                cell0.setCellValue(resultSet.getString("user"));
                Cell cell1 = row.createCell(1);
                cell1.setCellValue(resultSet.getString("times"));
                Cell cell2 = row.createCell(2);
                cell2.setCellValue(resultSet.getString("name"));
                Cell cell3 = row.createCell(3);
                cell3.setCellValue(resultSet.getString("price"));
                Cell cell4 = row.createCell(4);
                cell4.setCellValue(resultSet.getString("merchant"));
                Cell cell5 = row.createCell(5);
                cell5.setCellValue(resultSet.getString("producer"));
                rownum++;
            }

            File file = new File("customerPersonalOrders.xls");
            FileOutputStream out = new FileOutputStream(file);
            workbook.write(out);
            out.close();
            JOptionPane.showMessageDialog(null,"导出成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeAll(connection, statement, resultSet);
        }
    }


    //导出商家订单
    public void exportMOrdersToExcel(User user) throws SQLException {
        Connection connection = DBHelper.getConn();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("merchantPersonalOrders");
        try {
            statement = connection.prepareStatement("SELECT * FROM corders where merchant = ?");
            statement.setString(1, String.valueOf(user.getName()));
            resultSet = statement.executeQuery();

            Row headerRow = sheet.createRow(0);
            Cell headerCell0 = headerRow.createCell(0);
            headerCell0.setCellValue("用户");
            Cell headerCell1 = headerRow.createCell(1);
            headerCell1.setCellValue("时间");
            Cell headerCell2 = headerRow.createCell(2);
            headerCell2.setCellValue("商品名");
            Cell headerCell3 = headerRow.createCell(3);
            headerCell3.setCellValue("价格");
            Cell headerCell4 = headerRow.createCell(4);
            headerCell4.setCellValue("商家");
            Cell headerCell5 = headerRow.createCell(5);
            headerCell5.setCellValue("生产者");

            int rownum = 1;
            while (resultSet.next()) {
                Row row = sheet.createRow(rownum);
                Cell cell0 = row.createCell(0);
                cell0.setCellValue(resultSet.getString("user"));
                Cell cell1 = row.createCell(1);
                cell1.setCellValue(resultSet.getString("times"));
                Cell cell2 = row.createCell(2);
                cell2.setCellValue(resultSet.getString("name"));
                Cell cell3 = row.createCell(3);
                cell3.setCellValue(resultSet.getString("price"));
                Cell cell4 = row.createCell(4);
                cell4.setCellValue(resultSet.getString("merchant"));
                Cell cell5 = row.createCell(5);
                cell5.setCellValue(resultSet.getString("producer"));
                rownum++;
            }

            File file = new File("merchantPersonalOrders.xls");
            FileOutputStream out = new FileOutputStream(file);
            workbook.write(out);
            out.close();
            JOptionPane.showMessageDialog(null,"导出成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeAll(connection, statement, resultSet);
        }
    }


    // 导出生产商订单
    public void exportPOrdersToExcel(User user) throws SQLException {
        Connection connection = DBHelper.getConn();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("producerPersonalOrders");
        try {
            statement = connection.prepareStatement("SELECT * FROM corders WHERE producer LIKE ?;");
            statement.setString(1, "%" + user.getName() + "%");//使用LIKE和通配符进行模糊匹配
            resultSet = statement.executeQuery();

            Row headerRow = sheet.createRow(0);
            Cell headerCell0 = headerRow.createCell(0);
            headerCell0.setCellValue("用户");
            Cell headerCell1 = headerRow.createCell(1);
            headerCell1.setCellValue("时间");
            Cell headerCell2 = headerRow.createCell(2);
            headerCell2.setCellValue("商品名");
            Cell headerCell3 = headerRow.createCell(3);
            headerCell3.setCellValue("价格");
            Cell headerCell4 = headerRow.createCell(4);
            headerCell4.setCellValue("商家");
            Cell headerCell5 = headerRow.createCell(5);
            headerCell5.setCellValue("生产者");

            int rownum = 1;
            while (resultSet.next()) {
                String producerName = resultSet.getString("producer");
                if (!producerName.contains("(未补货)") && !producerName.contains("(已补货)")) {
                    continue; // 如果商家名不包含补货状态，则跳过该记录
                }

                Row row = sheet.createRow(rownum);
                Cell cell0 = row.createCell(0);
                cell0.setCellValue(resultSet.getString("user"));
                Cell cell1 = row.createCell(1);
                cell1.setCellValue(resultSet.getString("times"));
                Cell cell2 = row.createCell(2);
                cell2.setCellValue(resultSet.getString("name"));
                Cell cell3 = row.createCell(3);
                cell3.setCellValue(resultSet.getString("price"));
                Cell cell4 = row.createCell(4);
                cell4.setCellValue(resultSet.getString("merchant"));
                Cell cell5 = row.createCell(5);
                cell5.setCellValue(producerName);
                rownum++;
            }

            File file = new File("producerPersonalOrders.xls");
            FileOutputStream out = new FileOutputStream(file);
            workbook.write(out);
            out.close();
            JOptionPane.showMessageDialog(null,"导出成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeAll(connection, statement, resultSet);
        }
    }



    // 导出所有订单
    public void exportAllOrdersToExcel() throws SQLException {
        Connection connection = DBHelper.getConn();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("allOrders");
        try {
            statement = connection.prepareStatement("SELECT * FROM corders;");
            resultSet = statement.executeQuery();

            Row headerRow = sheet.createRow(0);
            Cell headerCell0 = headerRow.createCell(0);
            headerCell0.setCellValue("订单号");
            Cell headerCell1 = headerRow.createCell(1);
            headerCell1.setCellValue("用户");
            Cell headerCell2 = headerRow.createCell(2);
            headerCell2.setCellValue("时间");
            Cell headerCell3 = headerRow.createCell(3);
            headerCell3.setCellValue("商品名");
            Cell headerCell4 = headerRow.createCell(4);
            headerCell4.setCellValue("价格");
            Cell headerCell5 = headerRow.createCell(5);
            headerCell5.setCellValue("商家");
            Cell headerCell6 = headerRow.createCell(6);
            headerCell6.setCellValue("生产者");

            int rownum = 1;
            while (resultSet.next()) {
                Row row = sheet.createRow(rownum);
                Cell cell0 = row.createCell(0);
                cell0.setCellValue(resultSet.getInt("orderNum"));
                Cell cell1 = row.createCell(1);
                cell1.setCellValue(resultSet.getString("user"));
                Cell cell2 = row.createCell(2);
                cell2.setCellValue(resultSet.getString("times"));
                Cell cell3 = row.createCell(3);
                cell3.setCellValue(resultSet.getString("name"));
                Cell cell4 = row.createCell(4);
                cell4.setCellValue(resultSet.getString("price"));
                Cell cell5 = row.createCell(5);
                cell5.setCellValue(resultSet.getString("merchant"));
                Cell cell6 = row.createCell(6);
                cell6.setCellValue(resultSet.getString("producer"));
                rownum++;
            }

            File file = new File("allOrders.xls");
            FileOutputStream out = new FileOutputStream(file);
            workbook.write(out);
            out.close();
            JOptionPane.showMessageDialog(null,"导出成功！");
            //添加日志
            User admin = new User();
            LogService.addLogs(admin,"管理员 " + admin + "导出了所有订单 ");

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeAll(connection, statement, resultSet);
        }
    }



}
